using System;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Services;
using System.Collections.Generic;
using GridControl;
/// <summary>
/// Summary description for MoviesService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[ScriptService]
public class MoviesService : System.Web.Services.WebService {

    public MoviesService () {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Xml)]
    public DataSet GetMovies(int limit, int start, string sort, string dir)
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;//ConnectionString
        con.Open();
        int page_size = 10;//default page size
        int offset = 1;//default page
        string sortCol, sortDir;
        if (string.IsNullOrEmpty(sort))
        {
            sortCol = "ID_MOVIE";
            sortDir = "ASC";
        }
        else
        {
            sortCol = sort;
            sortDir = dir;
        }
        page_size = limit;//int.Parse(limit);

        /*
         * if you use the PagingToolbar the start parameter 
         * is not refering to the start page but to the start record
        */
    
        offset = start / page_size;
       
        offset += 1;
        //query that gets only the records needed to the page
        // using the new ROW_NUMBER function in sql2005
        string sql = "WITH MOVIES AS ( " +
                    " SELECT ROW_NUMBER() OVER " +
                    "(ORDER BY " + sortCol + " " + sortDir + ")AS Row," +
                    " ID_MOVIE,TITLE, RATING,VOTES,YEAR,GENRE " +
                    " FROM TB_MOVIE )" +
                    " SELECT ID_MOVIE,TITLE, GENRE,RATING,VOTES,YEAR" +
                    " FROM MOVIES " +
                    " WHERE Row between (@PageIndex-1)* @PageSize+1  and @PageIndex*@PageSize";

        SqlCommand cmd = new SqlCommand(sql, con);
        //add the parameters to the query to grab the correct page
        cmd.Parameters.AddWithValue("@PageIndex", offset);
        cmd.Parameters.AddWithValue("@PageSize", page_size);
        SqlDataAdapter adapt = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        adapt.Fill(ds);

        //closes the objects and disposes

        GetMovieCount(ds);
        con.Close();
        cmd.Dispose();
        con.Dispose();
        return ds;

    }
    private void GetMovieCount(DataSet ds)
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;//ConnectionString
        con.Open();
        string sql = "SELECT COUNT(*) FROM TB_MOVIE";
        SqlCommand cmd = new SqlCommand(sql, con);

        int cont = int.Parse(cmd.ExecuteScalar().ToString());
        con.Close();
        //add a new datatable to the dataset
        DataTable dt = new DataTable();
        dt.Columns.Add("Total");
        DataRow dr = dt.NewRow();
        dr["Total"] = cont;
        dt.Rows.Add(dr);
        ds.Tables.Add(dt);
        cmd.Dispose();
        con.Dispose();
        
    }
    [WebMethod]
    public List<TreeNode> getNodes(string node)
    {
        List<TreeNode> nodes = new List<TreeNode>();
        int ini = int.Parse(node.Replace("node", ""));
        for (int i = ini + 1; i < ini + 10; i++)
        {
            TreeNode no = new TreeNode();
            no.draggable = true;
            no.id = i.ToString();
            no.text = "Node" + i;
            no.leaf = true;
            nodes.Add(no);
        }
        return nodes;
    }
}

